﻿/****************************** Module Header ******************************\
 * Module Name:  ExcelHelper.cs
 * Module Version:  1.0.0 (2012-11-27)
 * Copyright (c) AnyEB.Com (14507247@qq.com)
 * 
 * This class is used to encrypt string or encrypt number.
 * 
 * #2013-8-29 by sutroon 修正重构后无法保存数据的问题(ConnectionString构造问题)
 * 
 * This source is subject to the GPL License.
 * See http://www.gnu.org/licenses/gpl.html.
 * All other rights reserved.
 * 
 * THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
 * EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
 * WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Data.OleDb;

namespace UCL.Data
{
    /// <summary>
    /// Excel助理类
    /// </summary>
    public class ExcelHelper
    {
        private string filename;
        private string suffixname;
        public ExcelHelper(string filename, string suffix = "xls")
        {
            this.filename = filename;
            this.suffixname = suffix.ToLower();
        }

        public string GetConnectionString(bool firstRowIsTitle = true, string imex = " IMEX=1;")
        {
            switch (suffixname)
            {
                case "xlsx":
                    // 此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
                    return string.Format("Provider=Microsoft.Ace.OleDb.12.0; data source='{0}';Extended Properties='Excel 12.0; HDR={1};{2}'", filename, ((firstRowIsTitle) ? "Yes" : "No"), imex);
                default:
                    // 在以下连接字符串中，Extended Properties 关键字设置 Excel 特定的属性。“HDR=Yes;”指示第一行中包含列名，而不是数据，“IMEX=1;”通知驱动程序始终将“互混”数据列作为文本读取。Excel 8.0 针对Excel2000及以上版本，Excel5.0 针对Excel97。
                    return string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source='{0}'; Extended Properties='Excel 8.0; HDR={1};{2}'", filename, ((firstRowIsTitle) ? "Yes" : "No"), imex);
            }
        }

        public DataSet ExecuteDataSet(string sql = "select * from [Sheet1$]", bool firstRowIsTitle = true)
        {
            using (OleDbConnection conn = new OleDbConnection(GetConnectionString(firstRowIsTitle)))
            {
                conn.Open();
                OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
        }

        public DataTable ExecuteDataTable(string sql = "select * from [Sheet1$]", bool firstRowIsTitle = true)
        {
            DataSet ds = ExecuteDataSet(sql, firstRowIsTitle);
            return ds.Tables.Count == 0 ? new DataTable() : ds.Tables[0];
        }

        public int ExecuteNonQuery(string sql)
        {
            using (OleDbConnection conn = new OleDbConnection(GetConnectionString(true, string.Empty)))
            {
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = sql;
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 获取所有Sheet名称
        /// 2013-8-24 by sutroon
        /// </summary>
        /// <returns></returns>
        public IList<string> GetSheetNames()
        {
            IList<string> lst = new List<string>();
            using (OleDbConnection conn = new OleDbConnection(GetConnectionString(false)))
            {
                DataTable tb = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                foreach (DataRow row in tb.Rows)
                {
                    lst.Add(row["TABLE_NAME"].ToString());
                }
                return lst;
            }

        }

        private void CheckColumn(DataTable dt, DataTable dt_v)
        {
            foreach (DataRow dr in dt_v.Select())
            {
                if (!dt.Columns.Contains(dr["列名"].ToString()))
                {
                    dr.Delete();
                }
            }
            dt_v.AcceptChanges();
        }

        private string GetDataType(Type i)
        {
            string s;
            switch (i.Name)
            {
                case "String":
                    s = "Char";
                    break;
                case "Int32":
                    s = "Int";
                    break;
                case "Int64":
                    s = "Int";
                    break;
                case "Int16":
                    s = "Int";
                    break;
                case "Double":
                    s = "Double";
                    break;
                case "Decimal":
                    s = "Double";
                    break;
                default:
                    s = "Char";
                    break;
            }
            return s;
        }

        private OleDbType StringToOleDbType(Type i)
        {
            OleDbType s;

            switch (i.Name)
            {
                case "String":
                    s = OleDbType.Char;
                    break;
                case "Int32":
                    s = OleDbType.Integer;
                    break;
                case "Int64":
                    s = OleDbType.Integer;
                    break;
                case "Int16":
                    s = OleDbType.Integer;
                    break;
                case "Double":
                    s = OleDbType.Double;
                    break;
                case "Decimal":
                    s = OleDbType.Decimal;
                    break;
                default:
                    s = OleDbType.Char;
                    break;
            }
            return s;
        }

        public string GetCreateTableSql(DataTable dt, string sheetName)
        {
            if (dt.Columns.Count == 0) return string.Empty;
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("CREATE TABLE {0} (", sheetName);
            foreach (DataColumn c in dt.Columns)
            {
                sb.AppendFormat("[{0}] {1},", c.ColumnName, GetDataType(c.DataType));
            }
            return string.Format("{0})", sb.ToString().TrimEnd(','));
        }

        /// <summary>
        /// 写入数据
        /// 2013-8-24 by sutroon
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="fields"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public string Insert(string sheetName, string fields, string values)
        {
            using (OleDbConnection conn = new OleDbConnection(GetConnectionString(false)))
            {
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = string.Format("insert into [{0}$] ({1}) values ({2})", sheetName, fields, values);
                    try
                    {
                        cmd.ExecuteNonQuery();
                        return "success";
                    }
                    catch (Exception ex)
                    {
                        return ex.Message;
                    }
                }
            }
        }

        // 生成 InsertCommand 并设置参数
        public OleDbCommand GetInsertCommand(string sheetName, DataTable dt, OleDbConnection conn)
        {
            StringBuilder sb = new StringBuilder();
            OleDbCommand i;
            sb.AppendFormat("INSERT INTO [{0}$] (", sheetName);
            foreach (DataColumn c in dt.Columns)
            {
                sb.AppendFormat("[{0}],", c.ColumnName);
            }
            sb = sb.Remove(sb.Length - 1, 1);
            sb.AppendFormat(") VALUES (");
            foreach (DataColumn c in dt.Columns)
            {
                sb.Append("?,");
            }
            sb = sb.Remove(sb.Length - 1, 1);
            sb.Append(")");

            i = new OleDbCommand(sb.ToString(), conn);
            foreach (DataColumn c in dt.Columns)
            {
                i.Parameters.Add("@" + c.Caption, StringToOleDbType(c.DataType), 0, c.Caption);
            }
            return i;
        }

        /// <summary>
        /// 保存到指定文件中
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sheetName"></param>
        /// <param name="filter"></param>
        /// <returns></returns>
        public string SaveToExcel(DataTable dt, string sheetName = "Sheet1", string filter = "")
        {
            if (dt == null) return "DataTable Is Null!";
            try
            {
                ExecuteNonQuery(GetCreateTableSql(dt, sheetName));
                using (OleDbConnection conn = new OleDbConnection(GetConnectionString(true, string.Empty)))
                {
                    OleDbDataAdapter da = new OleDbDataAdapter("Select * From [" + sheetName + "$]", conn);
                    DataTable dtExcel = new DataTable();
                    da.Fill(dtExcel);
                    da.InsertCommand = GetInsertCommand(sheetName, dt, conn);
                    DataRow row;
                    string ColumnName;
                    foreach (DataRow dr in dt.Select(filter))
                    {
                        row = dtExcel.NewRow();
                        foreach (DataColumn c in dt.Columns)
                        {
                            ColumnName = c.ColumnName;
                            row[ColumnName] = dr[ColumnName];
                        }
                        dtExcel.Rows.Add(row);
                    }
                    da.Update(dtExcel);
                }
                return "SUCCESS";

            }
            catch (OleDbException ex)
            {
                return ex.Message;
            }
        }
    }

}
